package com.b2c.repository.pdd;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.datacenter.DcShopEntity;
import com.b2c.entity.erp.ErpGoodsEntity;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.erp.vo.ShopGoodsSkuLinkErpSkuVo;
import com.b2c.entity.pdd.ShopGoodsEntity;
import com.b2c.entity.pdd.ShopGoodsSkuEntity;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.repository.Tables;

@Repository
public class PddGoodsRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    private static Logger log = LoggerFactory.getLogger(PddGoodsRepository.class);
    
    @Transactional
    public ResultVo<Long> addGoods(ShopGoodsEntity goodsEntity){
        var shop = jdbcTemplate.queryForObject("SELECT * FROM dc_shop where id=?",new BeanPropertyRowMapper<>(DcShopEntity.class),goodsEntity.getShopId());
        try{
            var shopGoods = jdbcTemplate.queryForObject("SELECT * FROM dc_shop_goods WHERE goodsId=?",new BeanPropertyRowMapper<>(ShopGoodsEntity.class),goodsEntity.getGoodsId() );
            log.info("goods is exist goodsID:"+goodsEntity.getGoodsId()+"开始检测规格");
            // jdbcTemplate.update("DELETE FROM dc_shop_goods_sku WHERE goodsId=?",goodsEntity.getGoodsId());
            // jdbcTemplate.update("DELETE FROM dc_shop_goods WHERE goodsId=?",goodsEntity.getGoodsId());
            for (var sku : goodsEntity.getSkuList()) {
                var skuT = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM dc_shop_goods_sku where skuId=?", Integer.class, sku.getSkuId());
                if(skuT == 0){
                    String skuSQL ="INSERT INTO dc_shop_goods_sku (shop_goods_id,skuId,goodsId,spec,skuQuantity,outerId,outerGoodsId,isSkuOnsale,erp_goods_spec_id,erp_goods_id,erp_goods_spec_code) VALUE(?,?,?,?,?,?,?,?,?,?,?)";
                    
                    Integer erp_goods_spec_id = 0;
                    Integer erp_goods_id = 0;
                    String erp_goods_spec_code="";
                    //查询spec
                    if(StringUtils.hasText(sku.getOuterId())){
                        List<ErpGoodsSpecEntity> spec = jdbcTemplate.query("SELECT * FROM erp_goods_spec WHERE specNumber=?", new BeanPropertyRowMapper(ErpGoodsSpecEntity.class), sku.getOuterId().trim());
                        if(spec!= null && spec.size()>0){
                            erp_goods_spec_id = spec.get(0).getId();
                            erp_goods_id  = spec.get(0).getGoodsId();
                            erp_goods_spec_code = spec.get(0).getSpecNumber();
                        }
                    }
                    
                        jdbcTemplate.update(skuSQL,
                        shopGoods.getId(),
                        sku.getSkuId(),
                        goodsEntity.getGoodsId(),
                        sku.getSpec(),
                        sku.getSkuQuantity(),
                        sku.getOuterId(),
                        sku.getOuterGoodsId(),
                        sku.getIsSkuOnsale(),
                        erp_goods_spec_id,erp_goods_id,erp_goods_spec_code
                        );
                    
                    log.info(goodsEntity.getGoodsId()+"新增新规格："+sku.getSpec());
                }
            }
            
            return new ResultVo<>(EnumResultVo.Exist,"已存在,更新新增规格");
        }catch(Exception e){

        }

        //查找goods
        Integer erpGoodsId = 0;
        try{
            //先查erpGoods
            var erpGoods = jdbcTemplate.queryForObject("SELECT * FROM erp_goods WHERE `number`=? ", new BeanPropertyRowMapper<>(ErpGoodsEntity.class),goodsEntity.getGoodsNum());
            erpGoodsId = erpGoods.getId();
        }catch(Exception e){
        }

        try {
            final Integer erpGoodsIdFinal = erpGoodsId;
            Integer shopType = shop.getType();
            //查询erp系统
            String sql = "INSERT INTO dc_shop_goods (goodsId,goodsName,thumbUrl,imageUrl,isMoreSku,isOnsale,shopId,shopType,publishTime,erp_goods_id,goodsNum) VALUE (?,?,?,?,?,?,?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setLong(1, goodsEntity.getGoodsId());
                    ps.setString(2, goodsEntity.getGoodsName());
                    ps.setString(3, goodsEntity.getThumbUrl());
                    ps.setString(4, goodsEntity.getImageUrl());
                    ps.setInt(5, goodsEntity.getIsMoreSku() == null ? 0:1);
                    ps.setInt(6, goodsEntity.getIsOnsale()==null?0:1);
                    ps.setInt(7, goodsEntity.getShopId());
                    ps.setInt(8, shopType);
                    ps.setString(9, goodsEntity.getPublishTime());
                    ps.setInt(10, erpGoodsIdFinal);
                    ps.setString(11, goodsEntity.getGoodsNum());
                    return ps;
                }
            }, keyHolder);

            Long shop_goods_id = keyHolder.getKey().longValue();
            if(goodsEntity.getSkuList()!= null ){
                
                String skuSQL ="INSERT INTO dc_shop_goods_sku (shop_goods_id,skuId,goodsId,spec,skuQuantity,outerId,outerGoodsId,isSkuOnsale,erp_goods_spec_id,erp_goods_id,erp_goods_spec_code) VALUE(?,?,?,?,?,?,?,?,?,?,?)";
                for (var item : goodsEntity.getSkuList()) {
                    Integer erp_goods_spec_id = 0;
                    Integer erp_goods_id = 0;
                    String erp_goods_spec_code="";
                    //查询spec
                    if(StringUtils.hasText(item.getOuterId())){
                        List<ErpGoodsSpecEntity> spec = jdbcTemplate.query("SELECT * FROM erp_goods_spec WHERE specNumber=?", new BeanPropertyRowMapper(ErpGoodsSpecEntity.class), item.getOuterId().trim());
                        if(spec!= null && spec.size()>0){
                            erp_goods_spec_id = spec.get(0).getId();
                            erp_goods_id  = spec.get(0).getGoodsId();
                            erp_goods_spec_code = spec.get(0).getSpecNumber();
                        }
                    }

                    jdbcTemplate.update(skuSQL,
                    shop_goods_id,
                    item.getSkuId(),
                    goodsEntity.getGoodsId(),
                    item.getSpec(),
                    item.getSkuQuantity(),
                    item.getOuterId(),
                    item.getOuterGoodsId(),
                    item.getIsSkuOnsale(),
                    erp_goods_spec_id,erp_goods_id,erp_goods_spec_code
                    );
                }
            }
            log.info("insert Goods SUCCESS,GoodsId:"+goodsEntity.getGoodsName());
            return new ResultVo<>(EnumResultVo.SUCCESS,goodsEntity.getGoodsId());
        }catch(Exception ex){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                log.error("新增商品错误错误信息："+ex.getMessage());
                return new ResultVo<>(EnumResultVo.Fail,"失败");
        }
    }

    @Transactional
    public PagingResponse<ShopGoodsEntity> getGoodsList(Integer shopType,Integer shopId,Integer pageIndex, Integer pageSize, String num, Long goodsId, Integer isOnsale) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS pg.*,g.cost_price,g.`name` as goodsNickName,sh.`name` as shopName  ");
        sb.append(" FROM ").append(" dc_shop_goods as pg ");
        sb.append(" LEFT JOIN erp_goods as g on g.id=pg.erp_goods_id ");
        sb.append(" LEFT JOIN dc_shop sh on sh.id= pg.shopId ");

        sb.append(" WHERE 1=1 ");
        if(shopType !=null && shopType.intValue() > 0){
            sb.append(" AND shopType=? ");
            params.add(shopType);
        }
        
        if(shopId !=null && shopId.intValue() > 0){
            sb.append(" AND shopId=? ");
            params.add(shopId);
        }

        if (StringUtils.isEmpty(num) == false) {
            sb.append(" AND (goodsNum=? or goodsId = ?) ");
            params.add(num);
            params.add(num);
        }

        if (!StringUtils.isEmpty(isOnsale)) {
            sb.append(" AND isOnsale = ? ");
            params.add(isOnsale);
        }


        sb.append(" ORDER BY publishTime desc");
        sb.append("  LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ShopGoodsEntity.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();

        if (list != null && list.size() > 0) {
            //查询item
            for (var g : list) {
                g.setSkuList(jdbcTemplate.query("SELECT * FROM  dc_shop_goods_sku WHERE goodsId=?", new BeanPropertyRowMapper<>(ShopGoodsSkuEntity.class), g.getGoodsId()));
            }
        }
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }
    
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }


    @Transactional
    public ResultVo<Integer> linkErpGoodsSpec(Long pddSkuId,String erpCode){
        var xhsGoodsId = jdbcTemplate.queryForObject("SELECT goodsId FROM dc_shop_goods_sku where skuId = ? ",Long.class,pddSkuId);
        ErpGoodsSpecEntity erpGoodsSpecEntity = null;
        String goodsNum = "";
        try {
            //查询erp系统goods_spec
            String sql = " SELECT id,goodsId FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=? ";
            erpGoodsSpecEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), erpCode);
            
            var goods = jdbcTemplate.queryForObject("SELECT number FROM "+Tables.ErpGoods+" WHERE id=?",new BeanPropertyRowMapper<>(ErpGoodsEntity.class),erpGoodsSpecEntity.getGoodsId());
            goodsNum = goods.getNumber();
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.DataExist, "ERP系统找不到商品规格数据："+erpCode);
        }

        jdbcTemplate.update("UPDATE dc_shop_goods_sku SET erp_goods_spec_id=?,erp_goods_id=?,erp_goods_spec_code=? WHERE skuId=?",erpGoodsSpecEntity.getId(),erpGoodsSpecEntity.getGoodsId(),erpCode,pddSkuId);
        jdbcTemplate.update("UPDATE dc_shop_goods SET goodsNum=?,erp_goods_id=? WHERE goodsId=? ",goodsNum,erpGoodsSpecEntity.getGoodsId(),xhsGoodsId);
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS",erpGoodsSpecEntity.getId());
    }

    public ResultVo<Long> updatePublishTime(Long id, String publishTime) {
        jdbcTemplate.update("UPDATE dc_shop_goods SET publishTime=? WHERE id=?", publishTime,id);
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    public ResultVo<Long> updateRemark(Long id, String remark) {
        jdbcTemplate.update("UPDATE dc_shop_goods SET remark=? WHERE id=?", remark,id);
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }
    public ResultVo<Long> updateIsOnsale(Long id) {
        jdbcTemplate.update("UPDATE dc_shop_goods SET isOnsale=1-isOnsale WHERE id=?",id);
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    public ResultVo<Long> updateTotalSales(Long id, Integer totalSales) {
        jdbcTemplate.update("UPDATE dc_shop_goods SET totalSales=? WHERE id=?",totalSales,id);
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    public ShopGoodsEntity getGoodsById(Long goodsId) {
        try {
            String sql = "SELECT * FROM dc_shop_goods WHERE id=? ";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ShopGoodsEntity.class),goodsId);
        } catch (Exception e) {
            return null;
        }
        
    }

    public List<ShopGoodsSkuEntity> getGoodsSkuListByGoodsId(Long goodsId) {
        String sql = "SELECT * FROM dc_shop_goods_sku WHERE shop_goods_id=? order by spec ASC";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(ShopGoodsSkuEntity.class), goodsId);
    }

    public ResultVo<Integer> upGoodsNumById(Long goodsId, String goodsNum) {
        try {
            //查询商品编码
            var goods = jdbcTemplate.queryForObject("SELECT * FROM erp_goods WHERE `number` = ?", new BeanPropertyRowMapper<>(ErpGoodsEntity.class),goodsNum);

            jdbcTemplate.update("UPDATE dc_shop_goods SET goodsNum=?,erp_goods_id=? WHERE id=?",goodsNum,goods.getId(),goodsId);
            return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.NotFound, "商品编码不存在");
        }
        
    }

    public ResultVo<Integer> linkErpSkuAll(Long goodsId, List<ShopGoodsSkuLinkErpSkuVo> skuLinkList) {
        var ind = 0;
        for (ShopGoodsSkuLinkErpSkuVo vo : skuLinkList) {
            var goodsSpec = jdbcTemplate.queryForObject("SELECT * FROM erp_goods_spec WHERE id=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),vo.getErpSkuId());
            jdbcTemplate.update("UPDATE dc_shop_goods_sku SET erp_goods_spec_id=?,erp_goods_id=?,erp_goods_spec_code=? where id=? ",goodsSpec.getId(),goodsSpec.getGoodsId(),goodsSpec.getSpecNumber(),vo.getShopGoodsSkuId());   
            ind++;
        }
        return new ResultVo<>(EnumResultVo.SUCCESS, "完成更新"+ind+"条");
    }
    
}
